package com.d9cloud.tsconsole.util.tool.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    public static int DEFAULT_COLOUMN_WIDTH = 17;
    public static final int EXPORT_MAX_LINE = 100000;//导出最大行数
    private static Logger logger = org.slf4j.LoggerFactory.getLogger(TemplateUtil.class);

    private static <T> SXSSFWorkbook createWorkBook(String title, String[] columnKeys, String[] columnNames, List<T> data, ExcelCellValueConvert convert) throws Exception{
        if(null == columnKeys || null == columnNames || columnKeys.length != columnNames.length){
            throw new Exception("column keys or column names error");
        }

        SXSSFWorkbook workbook = new SXSSFWorkbook(100000);//缓存
        workbook.setCompressTempFiles(true);

        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);

        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
//        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);

        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);

        // 生成一个表格
        SXSSFSheet sheet = workbook.createSheet();

        //设置列宽
        for(int i=0; i<columnNames.length; i++){
            int columnWidthBytes = columnNames[i].length() < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : columnNames[i].length();
            sheet.setColumnWidth(i, columnWidthBytes*256);
        }

        int rowIndex = 0;
        //判断title决定是否生成title行
        if(StringUtils.isNotEmpty(title)){
            //生成标题行
            SXSSFRow titleRow = sheet.createRow(rowIndex++);
            titleRow.createCell(0).setCellValue(title);
            titleRow.getCell(0).setCellStyle(titleStyle);
            int length = 2;
            if(columnNames.length - 1 > length){
                length = columnNames.length - 1;
            }
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, length));
        }

        //生成列头
        SXSSFRow headerRow = sheet.createRow(rowIndex++);
        for(int i=0; i<columnNames.length; i++){
            headerRow.createCell(i).setCellValue(columnNames[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }

        // 遍历集合数据，产生数据行，运行map及java bean反射
        for (T item : data){
            SXSSFRow row = sheet.createRow(rowIndex);
            for (int i = 0; i < columnKeys.length; i++){
                SXSSFCell newCell = row.createCell(i);
                String columnKey = columnKeys[i];
                Object cellValue = null;
                if(item instanceof Map){
                    cellValue = ((Map) item).get(columnKey);
                }else{
                    try {
                        Field field = item.getClass().getDeclaredField(columnKey);
                        if(null != field){
                            field.setAccessible(true);
                            cellValue = field.get(item);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }

                if(null != cellValue){
                    if(null != convert){
                        cellValue = convert.convert(columnKey, cellValue);
                    }
                    newCell.setCellValue(cellValue.toString());
                    newCell.setCellStyle(cellStyle);
                }else{
                    newCell.setCellValue("");
                    newCell.setCellStyle(cellStyle);
                }
            }
            rowIndex++;
        }
        return workbook;
    }
    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param title
     * @param columnKeys
     * @param columnNames
     * @param data
     * @param out
     * @param <T>
     */
    public static <T> void exportExcel(String title, String[] columnKeys, String[] columnNames, List<T> data, OutputStream out, ExcelCellValueConvert convert) throws Exception {
        SXSSFWorkbook workbook = createWorkBook(title,columnKeys,columnNames,data,convert);
        workbook.write(out);
        workbook.close();
        workbook.dispose();
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式到指定的服务器位置
     * @param title
     * @param columnKeys
     * @param columnNames
     * @param data
     * @param path
     * @param fileName
     * @param <T>
     */
    public static <T> void exportExcel(String title, String[] columnKeys, String[] columnNames, List<T> data, String path,String fileName, ExcelCellValueConvert convert) throws Exception {
        SXSSFWorkbook workbook = createWorkBook(title,columnKeys,columnNames,data,convert);
        File file = new File(path);
        if(!file.exists()){
            file.mkdirs();
            file.setWritable(true,false);
        }
        file = new File(path+fileName);
        file.createNewFile();
        OutputStream out = new FileOutputStream(file);
        workbook.write(out);
        workbook.close();
        workbook.dispose();
    }



    public static <T> void exportExcel(String title, String[] columnKeys, String[] columnNames, List<T> data, OutputStream out) throws Exception {
        exportExcel(title, columnKeys, columnNames, data, out, null);
    }

    /**
     * 读取excel(03,07,10等),txt文件
     * @param in
     * @param fName
     * @param paramCount:选用参数：模板变量个数，不涉及到变量传0
     * @return
     * @throws IOException
     */
    public static List<Map<Integer,Object>> readAllExcel(InputStream in, String fName,int paramCount,int keyCount) throws IOException {

        String extension = fName.lastIndexOf(".") == -1 ? "" : fName
                .substring(fName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {// 2003
            logger.info("读取excel2003文件内容");
            return read2003Excel(in,paramCount,keyCount);
        } else if("xlsx".equals(extension)){
            logger.info("读取03以上版本的excel文件");
            return readXlsx(in,paramCount,keyCount);
        }
            return null ;
    }

    /**
     * 读取2003excel
     *
     * @param in
     * @return
     */
    private static List<Map<Integer,Object>> read2003Excel(InputStream in,int paramCount,int keyCount)
            throws IOException {
        List<Map<Integer,Object>> dataList = new ArrayList<Map<Integer,Object>>();
        HSSFWorkbook wb = new HSSFWorkbook(in);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = null;
        HSSFCell cell = null;
        Object val = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化数字
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        int cellNum = 0;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //第一列（被叫号码）为空，过滤掉
            if(i>0 && !Check03RowNull(row)){
                continue ;
            }
            Map<Integer,Object> objList  =  new HashMap<Integer,Object>();
            //根据表头确定要遍历的列数
            if(i == 0){
                cellNum = row.getLastCellNum();
            }
           /* //有短信按键
            if(keyCount > 0){
                //根据表头确定要遍历的列数
                if(i == 0){
                    cellNum = row.getLastCellNum();
                }
            }
            //没有短信按键就根据模板变量数来确定要遍历的列数
            else{
                cellNum = paramCount+1 ;
            }*/
            for (int j = row.getFirstCellNum(); j < cellNum; j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    val = " ";
					/*objList.add(val);*/
                    objList.put(j, val);
                    continue;
                }

                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                        val = cell.getStringCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            val = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle()
                                .getDataFormatString())) {
                            val = df.format(cell.getNumericCellValue());
                        } else {
                            val = sdf.format(HSSFDateUtil.getJavaDate(cell
                                    .getNumericCellValue()));
                        }
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        val = cell.getBooleanCellValue();
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:
                        val = "";
                        break;
                    default:
                        val = cell.toString();
                        break;
                }
				/*objList.add(val);*/
                objList.put(j, val);
            }
            dataList.add(objList);
        }
        return dataList;
    }

    public static List<Map<Integer,Object>> readXlsx(InputStream in,int paramCount,int keyCount) throws IOException {

        List<Map<Integer,Object>> dataList = new ArrayList<Map<Integer,Object>>();
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        Object val = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化数字
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        int cellNum = 0;
        for (int i = sheet.getFirstRowNum(); i <= sheet
                .getLastRowNum(); i++) {
            row = sheet.getRow(i);

            if (row == null) {
                continue;
            }
            //过滤空行
            if(i>0 && !Check07RowNull(row)){
                continue ;
            }
            Map<Integer,Object> objList  =  new HashMap<Integer,Object>();

            //根据表头确定要遍历的列数
            if(i == 0){
                cellNum = row.getLastCellNum();
            }
           /* //有短信按键
            if(keyCount > 0){
                //根据表头确定要遍历的列数
                if(i == 0){
                    cellNum = row.getLastCellNum();
                }
            }
            //没有短信按键就根据模板变量数来确定要遍历的列数
            else{
                cellNum = paramCount+1 ;
            }*/

            for (int j = row.getFirstCellNum(); j>-1 && j < cellNum; j++) {

                cell = row.getCell(j);
                if (cell == null) {
                    val = " ";
                    objList.put(j, val);
                    continue;
                }

                //cell.setCellValue("abc");
                cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为文本类型


                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        val = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:

                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            val = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle()
                                .getDataFormatString())) {
                            val = df.format(cell.getNumericCellValue());
                        } else {
                            val = sdf.format(HSSFDateUtil.getJavaDate(cell
                                    .getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        val = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        val = "";
                        break;
                    default:
                        val = cell.toString();
                        break;
                }
                if(StringUtils.isNotBlank(val.toString())){
                    objList.put(j, val);
                }
                else{
                    objList.put(j, " ");
                }
            }
            dataList.add(objList);
        }

        return dataList;
    }


    //判断行为空
    private static boolean Check03RowNull(HSSFRow hssfRow){
        int num = 0;
        int count = 0;
        Iterator<Cell> cellItr =hssfRow.iterator();
        while(cellItr.hasNext()){
            Cell c =cellItr.next();
            if(count == 0 && c.getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                num++;
            }
            count++;
        }
        if(count == 0 || num > 0){
            return false;
        }
        return true;
    }

    //第一列被叫号码为空或者一个单元格都没，过滤掉行
    private static boolean Check07RowNull(XSSFRow xssfRow){
        int num = 0;
        int count = 0;
        Iterator<Cell> cellItr =xssfRow.iterator();
        while(cellItr.hasNext()){
            Cell c =cellItr.next();
            if(count ==0 && c.getCellType() ==XSSFCell.CELL_TYPE_BLANK){
                num++;
            }
            count++;
        }
        if(count == 0 || num > 0){
            return false;
        }
        return true;
    }

}